package com.elite.groovy.callback

import com.elite.groovy.util.EliteBean
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * 在线监控页面定时任务
 * 2021/8/5
 */

public class apiOnLineMonitor implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiOnLineMonitor.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        long startTime = System.currentTimeMillis();
        long endTime = System.currentTimeMillis();

        log.debug("3Vjia网聊监控页面统计定时任务启动任务content...");
        try {
            // 处理排队时间超时但状态还是0的数据
            /*
            更新规则：转人工+客户发起+结束时间为空+Request_Status_Id+根据当前时间排队时间已经大于路由超时+一天以内的数据
             */
            String updateTimeOut = '''Update Chat_Request R1,
       (Select Distinct R1.Id, Ifnull(R3.Value, R4.Value) As Route_Time_Out
          From Queue R1
          Left Join Workgroup R2
            On R1.Workgroup_Id = R2.Id
          Left Join Property_Value R3
            On R2.Id = R3.Property_Key
           And R3.Property_Id = 8
          Left Join Property_Value R4
            On R4.Property_Key = '-1'
           And R4.Property_Id = 8) R2
   Set R1.End_Time = R1.Creation_Time + R2.Route_Time_Out * 1000, R1.Request_Status_Id = 3, R1.App_Source = R1.Creation_Time + R2.Route_Time_Out * 1000
 Where R1.Queue_Id = R2.Id
   And R1.Request_Status_Id = 0
   And R1.End_Time Is Null
   And R1.Sender_Type In (0, 2)
   And R1.Creation_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval -1 Day)), '.', '') * 1000
   And Round((Ifnull(R1.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - R1.Creation_Time) / 1000) > R2.Route_Time_Out
   And ((R1.Request_Type_Id = 4 And R1.From_Chat_User_Id Like 'BOT%') Or
       (R1.Request_Type_Id = 1 And Length(R1.From_Chat_User_Id) > 6 And R1.Sender_Type In (0, 2) And
       (R1.To_Chat_User_Id Not Like 'BOT%' Or R1.To_Chat_User_Id Is Null)))''';

            int updateR = eliteBean.update(updateTimeOut, "dataSource");
            log.debug("3Vjia处理排队时间超时但状态还是0的数据返回-耗时：" + (System.currentTimeMillis() - startTime) + "【结果List】：" + updateR);

            // 当前小时数
            String selectSql = '''Select R1.Data_Fd,
       R1.Hour_Fd,
			 r1.group_id,
       Sum(R1.Total_Request_Num) As Total_Request_Num,
       Sum(R1.Total_Session_Num) As Total_Session_Num, -- 总会话数
        Sum(R1.Robot_Valid_Num)-Sum(R1.robotnot)+Sum(R1.Staff_Valid_Num) As Total_Valid_Num, -- 总有效会话数:【机器人有效会话数】+【人工有效会话数】
       Sum(R1.Robot_Valid_Num)-Sum(R1.robotnot) As Robot_Valid_Num, -- 机器人有效会话数
       Sum(R1.Staff_Valid_Num) As Staff_Valid_Num, -- 人工有效会话数
       Sum(R1.Wait_In_Num) As Wait_In_Num, -- 排队会话数
       Case
         When Sum(R1.Staff_Valid_Num) > 0 Then
          Concat(Round(Sum(R1.Wait_In_Num) / Sum(R1.Staff_Valid_Num) * 100, 2), '%')
         Else
          '0.00%'
       End As Wait_In_Rate, -- 排队率:排队时间大于3s的会话数/(机器人转人工会话数+直接进入技能组会话数)
       Sum(R1.Wait_In_Num_30) As Wait_In_Num_30,
       Case
         When Sum(R1.Staff_Valid_Num) > 0 Then
          Concat(Round(Sum(R1.Wait_In_Num_30) / Sum(R1.Staff_Valid_Num) * 100, 2), '%')
         Else
          '0.00%'
       End As Wait_In_Num_30_Rate, -- 30秒接通率:会话排队时间小于等于30秒的接通内接通数量/(机器人转人工会话数+ 直接进入技能组会话数) 
       Sum(R1.Wait_In_Num_60) As Wait_In_Num_60,
       Case
         When Sum(R1.Staff_Valid_Num) > 0 Then
          Concat(Round(Sum(R1.Wait_In_Num_60) / Sum(R1.Staff_Valid_Num) * 100, 2), '%')
         Else
          '0.00%'
       End As Wait_In_Num_60_Rate, -- 60秒接通率:会话排队时间小于等于60秒接通内接通数量/(机器人转人工会话数+ 直接进入技能组会话数)
       Sum(R1.Wait_In_Num) - Sum(R1.Wait_To_Staff) As Total_Wait_Off_Num, -- 排队离开会话数
       Sum(R1.Total_Wait_In_Time) As Total_Wait_In_Time, -- 总排队时间
       Case
         When Sum(R1.Staff_Valid_Num) > 0 Then
          Sec_To_Time(Round(Sum(R1.Total_Wait_In_Time) / Sum(R1.Staff_Valid_Num)))
         Else
          '00:00:00'
       End As Avg_Wait_In_Time, -- 平均排队时长: 排队时间大于3s的会话的排队总时长/(机器人转人工会话数+直接进入技能组会话数)
       Max(R1.Max_Wait_In_Time) As Max_Wait_In_Time, -- 最长排队时间: 所有会话中的排队时间的最大值
       sum(unresolved_num) as unresolved_num -- 一次解决数
  From (Select Cr.Chat_Session_Id,w.id as group_id,
               From_Unixtime(Cr.Creation_Time / 1000, '%Y-%m-%d') As Data_Fd,
               From_Unixtime(Cr.Creation_Time / 1000, '%H') As Hour_Fd,
               Count(Cr.Id) As Total_Request_Num, -- 总请求数
               Count(Distinct Case
                       When ((Cr.Request_Type_Id = 1) Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')
                        or Cr.Chat_Session_Id is null) Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Total_Session_Num, -- 总会话数
               Count(Distinct Case
                       When (R1.Queue_Id Or (Cr.Request_Type_Id = 1 And Cr.Request_Status_Id = 12)) Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Robot_Valid_Num, -- 机器人有效数
                     ifnull(sum(r3.robotnot),0) robotnot, -- 机器人无效数
               Sum(Case
                     When ((Cr.Request_Type_Id = 1 And Cr.Request_Status_Id = 1) Or
                          (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%' And Cr.To_Chat_User_Id Is Not Null)) Then
                      1
                     Else
                      0
                   End) As Staff_Valid_Num, -- 人工有效数
               Count(Distinct Case
                       When R1.Queue_Id Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Robot_To_Staff, -- 机器人转人工会话数
               Sum(Case
                     When Cr.Request_Type_Id = 1 And Cr.Request_Status_Id < 8 Then
                      1
                     Else
                      0
                   End) As Customer_To_Staff, -- 直接转人工会话数
               Count(Distinct Case
                       When (Cr.Request_Type_Id = 1 Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')) And
                            Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000) >= 0 Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Wait_In_Num, -- 排队等待数(所有排队时间大于Chat_Queue_Time秒的会话数)
               Count(Distinct Case
                       When (Cr.Request_Type_Id = 1 Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')) And
                            Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000) <= 30 And
                            Cr.Request_Status_Id = 1 Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Wait_In_Num_30, -- 30秒接通量(会话排队时间小于等于30秒的接通内接通数量)
               Count(Distinct Case
                       When (Cr.Request_Type_Id = 1 Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')) And
                            Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000) <= 60 And
                            Cr.Request_Status_Id = 1 Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Wait_In_Num_60, -- 60秒接通量(会话排队时间小于等于60秒的接通内接通数量)
               Count(Distinct Case
                       When ((Cr.Request_Type_Id = 1 Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')) And Cr.Request_Status_Id = 1) And
                            Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000) >= 0 Then
                        Ifnull(Cr.Chat_Session_Id, @Rownum := @Rownum + 1)
                       Else
                        Null
                     End) As Wait_To_Staff, -- 排队接通会话数
               Sum(Distinct Case
                     When ((Cr.Request_Type_Id = 1 Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')) And Cr.Request_Status_Id = 1) And
                          Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000) >= 0 Then
                      Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000)
                     Else
                      0
                   End) As Total_Wait_In_Time, -- 总排队时间(排队时间大于3s的会话的排队总时长)
               Max(Distinct Case
                     When ((Cr.Request_Type_Id = 1 Or (Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%')) And Cr.Request_Status_Id = 1) And
                          Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000) >= 0 Then
                      Round((Ifnull(Cr.End_Time, Replace(Unix_Timestamp(Now(3)), '.', '')) - Cr.Creation_Time - R2.Chat_Queue_Time) / 1000)
                     Else
                      0
                   End) As Max_Wait_In_Time, -- 最长等待时间(所有会话中的排队时间的最大值)
               IFNULL(unresolved_num,0) as unresolved_num
          From (Select (@Rownum := 0)) b, Chat_Request Cr
          Left Join (Select Chat_Session_Id, Creation_Time, Queue_Id, Rank1, To_Chat_User_Id
                      From (Select t.Chat_Session_Id,
                                   t.Creation_Time,
                                   t.Queue_Id,
                                   @Rownum := @Rownum + 1,
                                   If(@Order_Num = t.Chat_Session_Id, @Rank1 := @Rank1 + 1, @Rank1 := 1) As Rank1,
                                   @Order_Num := t.Chat_Session_Id,
                                   To_Chat_User_Id
                              From (Select Chat_Session_Id, Creation_Time, Queue_Id, To_Chat_User_Id
                                      From Chat_Request Cr
                                     Where Request_Type_Id = 4
                                       And From_Chat_User_Id Like 'BOT%'
                                       And From_Unixtime(Creation_Time / 1000, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d')
                                     Order By Chat_Session_Id Asc, Creation_Time Desc) t,
                                   (Select @Rownum := 0, @Order_Num := Null, @Rank1 := 0) a) Result
                     Where Rank1 = 1
                     Order By Chat_Session_Id Desc) R1
            On R1.Chat_Session_Id = Cr.Chat_Session_Id
          Left Join (Select Cast(Ifnull(Parametervalue, 3) As Signed) Chat_Queue_Time
                      From Sysc_Parameter
                     Where Parameter_Id = '3VTQUT'
                       And Rolegroup_Id = '######' Limit 1) R2
            On 1 = 1
          left join (
                     select cr.CHAT_SESSION_ID,count(1) as robotnot
                        from chat_request cr
                        left join chat_message cm on cm.chat_session_id=cr.chat_session_id
                        where cr.to_chat_user_id like '%BOT%' and cm.chat_session_id is null
                        And cr.Creation_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval -1 Day)), '.', '') * 1000
                        GROUP BY cr.CHAT_SESSION_ID
					)r3 on R3.Chat_Session_Id = Cr.Chat_Session_Id
		  LEFT JOIN (
					 select chat_session_id,sum(chat_unresolved_num)as unresolved_num  from chat_one_solution 
						 where chat_creaddate> Date_Add(Now(), Interval -1 Day) GROUP BY chat_session_id
					 )r4 on r4.chat_session_id=cr.chat_session_id
					  left join queue q on q.id=cr.queue_id
					 left join workgroup w on w.id=q.workgroup_id
         Where Cr.Request_Type_Id != 3
           And cr.Creation_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval -1 Day)), '.', '') * 1000
         Group By Cr.Chat_Session_Id,w.id ,
               From_Unixtime(Cr.Creation_Time / 1000, '%Y-%m-%d') ,
               From_Unixtime(Cr.Creation_Time / 1000, '%H'),IFNULL(unresolved_num,0)
         Order By Cr.Chat_Session_Id Desc) R1
 Group By R1.Data_Fd, R1.Hour_Fd, r1.group_id
''';

            List selectList = eliteBean.chaxun_sql(selectSql, "dataSourceR");
            endTime = System.currentTimeMillis();
            log.debug("3Vjia在线监控页面统计定时任务查询返回-耗时：" + (System.currentTimeMillis() - startTime) + "【长度】:" + selectList.size() + "【结果List】：" + selectList.toString());

            String[] doSaveResultSQL = new String[selectList.size()];

            int k = 0;
            for (int i = 0; i < selectList.size(); i++) {

                Map selectMap = selectList.get(i);

                String Data_Fd = selectMap.get("Data_Fd").toString();
                String Hour_Fd = selectMap.get("Hour_Fd").toString();
                String group_id = selectMap.get("group_id").toString();
                String Total_Request_Num = selectMap.get("Total_Request_Num").toString();
                String Total_Session_Num = selectMap.get("Total_Session_Num").toString();
                String Total_Valid_Num = selectMap.get("Total_Valid_Num").toString();
                String Robot_Valid_Num = selectMap.get("Robot_Valid_Num").toString();
                String Staff_Valid_Num = selectMap.get("Staff_Valid_Num").toString();
                String Wait_In_Num = selectMap.get("Wait_In_Num").toString();
                String Wait_In_Rate = selectMap.get("Wait_In_Rate").toString();
                String Wait_In_Num_30 = selectMap.get("Wait_In_Num_30").toString();
                String Wait_In_Num_30_Rate = selectMap.get("Wait_In_Num_30_Rate").toString();
                String Wait_In_Num_60 = selectMap.get("Wait_In_Num_60").toString();
                String Wait_In_Num_60_Rate = selectMap.get("Wait_In_Num_60_Rate").toString();
                String Total_Wait_Off_Num = selectMap.get("Total_Wait_Off_Num").toString();
                String Total_Wait_In_Time = selectMap.get("Total_Wait_In_Time").toString();
                String Avg_Wait_In_Time = selectMap.get("Avg_Wait_In_Time").toString();
                String Max_Wait_In_Time = selectMap.get("Max_Wait_In_Time").toString();
                String unresolved_num = selectMap.get("unresolved_num").toString();

                log.debug("3Vjia在线监控页面统计定时任务当前处理Data_Fd：" + Data_Fd + " Hour_Fd:" + Hour_Fd);

                String selectCheck = "select id from 3vj_wechat_online_monitor where monitor_date = '" + Data_Fd + "' and monitor_date_hour = '" + Hour_Fd + "'";

                List selectCheckList = eliteBean.chaxun_sql(selectCheck, "dataSource");
                if (selectCheckList.size() > 0) {


                    for (int j = 0; j < selectCheckList.size(); j++) {
                        Map selectCheckMap = selectCheckList.get(j);
                        String m_ID = selectCheckMap.get("id");
                        // 更新当前时间 记录
                        String upSql = "Update 3vj_Wechat_Online_Monitor" +
                                "   Set Total_Request_Num     = '" + Total_Request_Num + "'," +
                                "       Total_Session_Num     = '" + Total_Session_Num + "'," +
                                "       workgroup_id     = '" + group_id + "'," +
                                "       Total_Valid_Num     = '" + Total_Valid_Num + "'," +
                                "       Robot_Valid_Num     = '" + Robot_Valid_Num + "'," +
                                "       Staff_Valid_Num     = '" + Staff_Valid_Num + "'," +
                                "       Wait_In_Num         = '" + Wait_In_Num + "'," +
                                "       Wait_In_Rate         = '" + Wait_In_Rate + "'," +
                                "       Wait_In_Num_30      = '" + Wait_In_Num_30 + "'," +
                                "       Wait_In_Num_30_Rate      = '" + Wait_In_Num_30_Rate + "'," +
                                "       Wait_In_Num_60      = '" + Wait_In_Num_60 + "'," +
                                "       Wait_In_Num_60_Rate      = '" + Wait_In_Num_60_Rate + "'," +
                                "       Total_Wait_Off_Num  = '" + Total_Wait_Off_Num + "'," +
                                "       Total_Wait_In_Time = '" + Total_Wait_In_Time + "'," +
                                "       Avg_Wait_In_Time = '" + Avg_Wait_In_Time + "'," +
                                "       Max_Wait_In_Time    = '" + Max_Wait_In_Time + "'," +
                                "       unresolved_num    = '" + unresolved_num + "'," +
                                "       Monitor_Time        = Now()" +
                                " Where Id = '" + m_ID + "'";
                        log.debug('更新当前时间的记录SQL' + upSql);
                        doSaveResultSQL[i] = upSql;
                    }
                } else {

                    // 插入该条记录
                    String insertSql = "Insert Into 3vj_Wechat_Online_Monitor" +
                            "  (Id," +
                            "   Total_Request_Num," +
                            "   Total_Session_Num," +
                            "   workgroup_id," +
                            "   Total_Valid_Num," +
                            "   Robot_Valid_Num," +
                            "   Staff_Valid_Num," +
                            "   Wait_In_Num," +
                            "   Wait_In_Rate," +
                            "   Wait_In_Num_30," +
                            "   Wait_In_Num_30_Rate," +
                            "   Wait_In_Num_60," +
                            "   Wait_In_Num_60_Rate," +
                            "   Total_Wait_Off_Num," +
                            "   Total_Wait_In_Time," +
                            "   Avg_Wait_In_Time," +
                            "   Max_Wait_In_Time," +
                            "   Monitor_Date," +
                            "   Monitor_Date_Hour," +
                            "   unresolved_num," +
                            "   Monitor_Time)" +
                            " Values" +
                            "  (Uuid()," +
                            "   '" + Total_Request_Num + "'," +
                            "   '" + Total_Session_Num + "'," +
                            "   '" + group_id + "'," +
                            "   '" + Total_Valid_Num + "'," +
                            "   '" + Robot_Valid_Num + "'," +
                            "   '" + Staff_Valid_Num + "'," +
                            "   '" + Wait_In_Num + "'," +
                            "   '" + Wait_In_Rate + "'," +
                            "   '" + Wait_In_Num_30 + "'," +
                            "   '" + Wait_In_Num_30_Rate + "'," +
                            "   '" + Wait_In_Num_60 + "'," +
                            "   '" + Wait_In_Num_60_Rate + "'," +
                            "   '" + Total_Wait_Off_Num + "'," +
                            "   '" + Total_Wait_In_Time + "'," +
                            "   '" + Avg_Wait_In_Time + "'," +
                            "   '" + Max_Wait_In_Time + "'," +
                            "   '" + Data_Fd + "'," +
                            "   '" + Hour_Fd + "'," +
                            "   '" + unresolved_num + "'," +
                            "   Now())";
                    log.debug('插入当前时间的记录SQL' + insertSql);
                    doSaveResultSQL[i] = insertSql;

                }

                k = k + 1;
            }

            if (k > 0) {
                List doSaveResultSQLList = eliteBean.updateBatch(doSaveResultSQL, "dataSource");
                log.debug("3Vjia在线监控页面统计定时任务处理End-耗时:" + (System.currentTimeMillis() - endTime) + " 结果:" + doSaveResultSQLList.toString());
            } else {

                log.warn("3Vjia在线监控页面统计定时任务处理End...SQL空");
            }

            log.debug("3Vjia在线监控页面统计定时任务处理结束-总耗时:" + (System.currentTimeMillis() - startTime));

            return 1;

        } catch (Exception e) {
            log.error("3Vjia在线监控页面统计定时任务操作异常", e);
            return 0;
        }
    }

}